<?php

namespace app\admin\controller;

use app\admin\model\StudentModel;
use app\admin\model\RoomModel;
use app\admin\model\SchoolModel;
use cmf\controller\AdminBaseController;
use think\Controller;
use think\Db;
use think\Db\Query;
use think\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class StudentController extends AdminBaseController
{
    public function index()
    {
        $name = $this->request->param('name');//获取前端name变量
        $school_id = $this->request->param('school_id');//获取school_id
        $room = Db::name('room')//链接数据库班级表
        ->where('school_id', $school_id)//接受school_id
        ->select();
        $room_id = input('room_id');//提取表单中的room_id
        $school = Db::name('school')->select();//读取数据库学校表
        $student = Db::name('student')//链接学生表
        ->join('jf_room', 'jf_student.room_id=jf_room.id', 'LEFT')//让roomID=student表中room_id
        ->join('jf_school', 'jf_room.school_id=jf_school.id', 'LEFT')//让schoolID=student里school_id
        ->field('jf_student.*,jf_school.name as school_name,jf_room.name as room_name,jf_school.id as school_id')//查询所有班级 学校 学生 重命名
        ->group('jf_student.id')//分组
        //模糊查询
        ->where(function (Query $query) use ($name, $school_id, $room_id) {
            if ($name) {
                $query->where('jf_student.name', 'like', "%$name%");
            }
            if ($school_id) {
                $query->where('jf_school.id', 'eq', $school_id);
            }
            if ($room_id) {
                $query->where('jf_room.id', 'eq', $room_id);
            }
        })
            ->order("id DESC")//根据ID倒叙
            ->paginate(3, false, ['query' => request()->param()]);//分页
        $page = $student->render();//分页样式
        //数组方式获取 映射页面 接受返回值
        return view('index', ['student' => $student,
            'page' => $page,
            'school_id' => $school_id,
            'room_id' => $room_id,
            'school' => $school,
            'room' => $room]);
    }
    /**
     * 显示创建资源表单页.
     *
     * @return \think\Response
     */
    public function add()
    {
        $school=SchoolModel::select();
        $room=RoomModel::select();//添加查询学校表，班级表
        return $this->fetch('',['school'=>$school,'room'=>$room]);//映射页面
    }
    public function addPost()
    {
        $data = input();//接受HTML input提交的值
        $data['create_time'] = date('Y-m-d H:i:s',time());//根据时间计算，来显示创建时间
        unset($data['photo_name']);//销毁photo_name
        $add=Db::name('student')->insert($data);//在数据库插入添加新数据方法
        if ($add){//判断是否添加成功
            $this ->success('添加成功','student/index');
        }else{
            $this->error('添加失败');
        }
    }
    public function getRoomBySchool()
    {
        $schoolId=input('school_id');//获取school_id
        $rooms=RoomModel::where('school_id',$schoolId)->select();//在room表中查询school的ID和值
        if ($rooms){
            $options=[];//默认学校值
            foreach ($rooms as $v){
                $opt="<option value='".$v->id."'>".$v->name."</option>";
                array_push($options,$opt);//按照顺序显示$opinion和foreach循环的值
            }
            return $options;//返回班级信息
        }else{
            return "<option value=''>暂无数据</option>";
        }
    }

    public function  getAdd(){
        $schoolId=input('school_id');//获取当前school_id
        $rooms=Db::name('room')//连接班级表
        ->where('school_id',$schoolId)//获取查询school_id值
        ->select();
        if ($rooms){
            $option=["<option value=''>请选择班级</option>"];//判断是否选中
            //循环room值
            foreach ($rooms as $val){
                $opt="<option value='".$val['id']."'>".$val['name']."</option>";
                array_push($option,$opt);//按照数组顺序显示循环的值
            }
            return $option;//返回班级信息值
        }else{
            return "<option value=''>暂无数据</option>";
        }
    }

    public function edit($id)
    {
        $student=StudentModel::join('jf_room','jf_student.room_id=jf_room.id','LEFT')
            ->join('jf_school','jf_room.school_id=jf_school.id','LEFT')
            ->field('jf_student.*')
            ->field('jf_room.id as room_id')
            ->field('jf_school.id as school_id')
            ->where(['jf_student.id'=>$id])
            ->select();
        $student=$student[0];
        //查询所有的记录
        $school=SchoolModel::all();
        $room=RoomModel::all(['school_id'=>$student['school_id']]);
        //实例化
        return view('',['school'=>$school,'student'=>$student,'room'=>$room]);


    }

    public function editpost(Request $request)
    {
        $data=$request->post();
        if (empty($data['name'])){
            $this->error('学生姓名不能为空');
        }
        if (empty($data['room_id'])){
            $this->error('班级不能为空');
        }
        //销毁数值元素
        unset($data['photo_name']);
        unset($data['school_id']);
        //函数格式化本地日期和时间，并返回格式化的日期字符串。
        $data['create_time']=date('Y-m-d H:i:s',time());
        if($data['image']){
            if ($data['oldImg']) {
                $path='D:/phpstudy_pro/WWW/jifen_cmf/public/upload/'.$data['oldImg'];
                //检查文件或目录是否存在。
                if (file_exists($path)){
                    //删除文件。
                    @unlink($path);
                }
            }
        }else{
            $data['image']=$data['oldImg'];
        }
        //按照返回的id查询数据库并更改数据
        $edit=StudentModel::update($data,['id'=>$data['id']]);
        if ($edit){
            //正确处理，默认跳转时间3秒
            $this->success('修改成功',url('student/index'));
        }else{
            //错误处理，默认跳转时间1秒
            $this->error('修改失败',url('student/index'));
        }
    }


    public function delete($id)
    {
        $data=input();//获取数据库班级id
        $del=StudentModel::destroy($data['id']);
        if ($del){
            $this->success("删除成功", url("student/index"));
        }else{
            $this->error("删除失败");
        }
    }
}